USE SAS
GO

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetContactDetails')
	BEGIN
		DROP  Procedure  dbo.GetContactDetails
	END
GO

CREATE Procedure [dbo].[GetContactDetails]
	@PKID		nvarchar(100) = NULL
,	@OWNERID	varchar(10) = ''
AS
BEGIN

	declare 
		@sql			varchar(max)
	,	@states			varchar(max)
	,	@accounts		varchar(max)
	,	@allUsers		varchar(max)
	,	@relations		varchar(max)

	SELECT	@sql = ''
		,	@states			= dbo.GetStatesXml()
		,	@accounts		= dbo.GetAccountsXml(@OWNERID)
		,	@allUsers		= dbo.GetAllUsersXml()
		,	@relations		= dbo.GetRelationsXML(@PKID)


	;WITH Con AS (
		SELECT 
			p.PartnerNo			AS ContactNo
		,	p.PartnerLastName	AS LastName
		,	p.PartnerFirstName	AS FirstName
		,	p.PartnerGUID		AS ContactGUID	
		,	p.IsActive			AS IsActive
		,	p.PartnerDivision	AS PARTNERDIVISIONID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			CAST(p.PartnerGUID AS VARCHAR(40)) = ISNULL(@PKID, '')
		UNION
		SELECT TOP 1
			'0'			AS ContactNo
		,	''			AS LastName
		,	''			AS FirstName
		,	NewID()		AS ContactGUID	
		,	'1'			AS IsActive
		,	'1'			AS PARTNERDIVISIONID
		FROM TR_PARTNER as p WITH (NOLOCK)
		WHERE
			ISNULL(@PKID, '') = ''
	), ConAddress AS (
		SELECT TOP 1
			a.AddressNo
		,	a.Street1
		,	a.Street2
		,	a.City
		,	a.State
		,	a.Zip
		,	a.AddressGUID
		,	p.ContactGUID
		FROM TR_ADDRESS as a WITH (NOLOCK)
		INNER JOIN Con as p WITH (NOLOCK)
			on p.ContactGUID = a.PartnerGUID
	), ConJob AS (
		SELECT TOP 1
			c.JobTitle
		,	c.Department
		,	p.ContactGUID
		FROM TR_CONTACT as c WITH (NOLOCK)
		INNER JOIN Con as p
			on p.ContactGUID = c.ContactGUID
	), ConComm AS (
		SELECT TOP 1
			c.CommunicationID
		,	c.Phone
		,	c.Fax
		,	c.CellPhone
		,	a.AddressGUID
		FROM TR_COMMUNICATION as c WITH (NOLOCK)
		INNER JOIN ConAddress as a
			on a.AddressGUID = c.AddressGUID
	), ConEmail AS (
		SELECT TOP 1
			e.PartnerEmail		AS Email
		,	e.EmailID
		,	p.ContactGUID
		FROM TR_EMAIL as e WITH (NOLOCK)
		INNER JOIN Con as p
			on p.ContactGUID = e.PartnerGUID
	), ConNotes AS (
		SELECT TOP 1
			n.NoteID
		,	n.NoteText
		,	n.SourceGUID
		FROM TR_NOTE as n with (NOLOCK)
		INNER JOIN Con as p with (NOLOCK)
			on p.ContactGUID = n.SourceGUID
	), ContactXML AS (
		SELECT 1 AS ID, N'<DATA>' AS Details 
		UNION
		SELECT 2 AS ID, N'<RESPONSE><RESULT>S</RESULT><MESSAGE></MESSAGE></RESPONSE>'
		UNION
		SELECT 2.1 as ID, @accounts as Details
		UNION
		SELECT 2.2 as ID, @allUsers as Details
		UNION
		SELECT 2.5 as ID, @states as Details
		UNION
		SELECT 2.9 as ID, @relations as Details

		UNION
		SELECT
			3 AS ID, 
		+	'<PKID><![CDATA[' + CAST(p.ContactGUID AS VARCHAR(40)) + ']]></PKID>'
		+	'<CONTACTGUID><![CDATA[' + CAST(p.CONTACTGUID AS VARCHAR(40)) + ']]></CONTACTGUID>'
		+	'<CONTACTNO><![CDATA[' + ISNULL(CAST(p.CONTACTNO AS VARCHAR), '0') + ']]></CONTACTNO>'
		+	'<LASTNAME><![CDATA[' + ISNULL(p.LASTNAME,'') + ']]></LASTNAME>'
		+	'<FIRSTNAME><![CDATA[' + ISNULL(p.FIRSTNAME,'') + ']]></FIRSTNAME>'
		+	'<STREET1><![CDATA[' + ISNULL(Street1,'') + ']]></STREET1>'
		+	'<STREET2><![CDATA[' + ISNULL(Street2,'') + ']]></STREET2>'
		+	'<CITY><![CDATA[' + ISNULL(City,'') + ']]></CITY>'
		+	'<STATE><![CDATA[' + ISNULL([State], '') + ']]></STATE>'
		+	'<ZIP><![CDATA[' + ISNULL(Zip,'') + ']]></ZIP>'
		+	'<JOBTITLE><![CDATA[' + ISNULL(j.JOBTITLE,'') + ']]></JOBTITLE>'
		+	'<DEPARTMENT><![CDATA[' + ISNULL(j.Department,'') + ']]></DEPARTMENT>'
		+	'<PHONE><![CDATA[' + ISNULL(Phone,'') + ']]></PHONE>'
		+	'<FAX><![CDATA[' + ISNULL(Fax,'') + ']]></FAX>'
		+	'<CELLPHONE><![CDATA[' + ISNULL(CellPhone,'') + ']]></CELLPHONE>'
		+	'<EMAIL><![CDATA[' + ISNULL(Email,'') + ']]></EMAIL>'
		+	'<NOTEID><![CDATA[' + CAST(ISNULL(n.NoteID,0) AS VARCHAR) + ']]></NOTEID>'
		+	'<NOTES><![CDATA[' + ISNULL(n.NOTETEXT,'') + ']]></NOTES>'
		+	'<ISACTIVE><![CDATA[' + (CASE WHEN ISNULL(IsActive,1) = 1 THEN 'X' ELSE '' END) + ']]></ISACTIVE>' 
		+	'<ADDRESSNO><![CDATA[' + ISNULL(CAST(a.AddressNo AS VARCHAR),'0')+ ']]></ADDRESSNO>' 
		+	'<ADDRESSGUID><![CDATA[' + ISNULL(CAST(a.ADDRESSGUID AS VARCHAR(40)),'')+ ']]></ADDRESSGUID>' 
		+	'<COMMUNICATIONID><![CDATA[' + ISNULL(CAST(c.COMMUNICATIONID AS VARCHAR),'0') + ']]></COMMUNICATIONID>' 
		+	'<EMAILID><![CDATA[' + ISNULL(CAST(EMAILID AS VARCHAR),'0') + ']]></EMAILID>' 
		+	'<PARTNERDIVISIONID><![CDATA[' + ISNULL(CAST(p.PARTNERDIVISIONID AS VARCHAR),'0')+ ']]></PARTNERDIVISIONID>' 
					AS Details
		FROM Con as p
		LEFT OUTER JOIN ConAddress as a
			ON a.ContactGUID = p.ContactGUID
		LEFT OUTER JOIN ConJob as j
			on j.ContactGUID = p.ContactGUID
		LEFT OUTER JOIN ConComm as c
			on c.AddressGUID = a.AddressGUID
		LEFT OUTER JOIN ConEmail as e
			on e.ContactGUID = p.ContactGUID
		LEFT OUTER JOIN ConNotes as n
			on n.SourceGUID = p.ContactGUID
		UNION
		SELECT 4 AS ID, '</DATA>' AS Details 
	)

	SELECT @sql = @sql + Details
	FROM ContactXML
	Order by ID

	select @sql	AS Detail
END
